﻿using OfficeOpenXml;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Aliphant.Tool.ExcelToJson.Helpers
{
    internal static class ExcelHelper
    {
        public static IDictionary<string, DataTable>? ReadToDataTable(string filePath)
        {
            IDictionary<string, DataTable> dtList = new Dictionary<string, DataTable>();
            try
            {
                ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
                using ExcelPackage package = new ExcelPackage(filePath);
                ExcelWorksheet worksheet = package.Workbook.Worksheets[0];

                var rowCount = worksheet.Dimension.End.Row;
                var columnCount = worksheet.Dimension.End.Column;

                string currentNode = "";

                for (int rowNum = 1; rowNum <= worksheet.Dimension.End.Row; rowNum++)
                {
                    var range = worksheet.Cells[rowNum, 1, rowNum, columnCount];
                    if (CommonHelper.IsEmptyRow(range))
                    {
                        continue;
                    }
                    string pn = worksheet.Cells[rowNum, 1].Text;
                    if (!string.IsNullOrEmpty(pn))
                    {
                        currentNode = pn;
                        DataTable dt = new DataTable(pn);
                        for (int i = 2; i <= columnCount; i++)
                        {
                            if (!string.IsNullOrEmpty(worksheet.Cells[rowNum, i].Text))
                            {
                                dt.Columns.Add(worksheet.Cells[rowNum, i].Text);
                            }
                        }
                        dtList[pn] = dt;
                    }
                    else
                    {
                        DataRow row = dtList[currentNode].Rows.Add();
                        foreach (var cell in range)
                        {
                            if (cell.Start.Column - 2 < 0) continue;
                            if (!string.IsNullOrEmpty(cell.Text))
                            {
                                row[cell.Start.Column - 2] = cell.Text;
                            }
                            else
                            {
                                row[cell.Start.Column - 2] = "";
                            }
                        }
                    }
                }
            }
            finally
            {

            }
            return dtList;
        }
    }
}
